﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Widgetsphere.Generator.Models;
using Widgetsphere.Generator.ProjectItemGenerators;
using System.Xml;
using Widgetsphere.Generator.Common.Util;
using Widgetsphere.Generator.Installer.ProjectItemGenerators;

namespace Widgetsphere.Generator.Installer
{
	internal static class SQLHelper
	{
		#region GetModelDifferenceSQL

		public static string GetModelDifferenceSQL(ModelRoot modelOld, ModelRoot modelNew)
		{
			StringBuilder sb = new StringBuilder();

			#region Loop and DELETE tables
			foreach (Table oldT in modelOld.Database.Tables)
			{
				Table[] tList = modelNew.Database.Tables.GetByKey(oldT.Key);
				if (tList.Length == 0)
				{
					//DELETE TABLE
					sb.AppendLine(SQLGeneration.GetSQLDropTable(oldT));
					sb.AppendLine("GO");
					sb.AppendLine();
				}
				else if (tList[0].DatabaseName != oldT.DatabaseName)
				{
					//RENAME TABLE
					sb.AppendLine("sp_rename [" + oldT.DatabaseName + "], [" + tList[0].DatabaseName + "]");
				}
			}
			#endregion

			#region Loop and Modify tables
			foreach (Table newT in modelNew.Database.Tables)
			{
				Table[] tList = modelOld.Database.Tables.GetByKey(newT.Key);
				if (tList.Length > 0)
				{
					Table oldT = tList[0];

					List<string> querylist = new List<string>();

					#region Rename table if need be
					if (oldT.DatabaseName != newT.DatabaseName)
					{
						//RENAME TABLE
						sb.AppendLine("sp_rename [" + oldT.DatabaseName + "], [" + newT.DatabaseName + "]");
					}
					#endregion

					#region Delete Columns
					foreach (Reference oldRef in oldT.Columns)
					{
						Column oldC = (Column)oldRef.Object;
						Column newC = Globals.GetColumnByKey(newT.Columns, oldC.Key);
						if (newC == null)
						{
							//DELETE COLUMN
							sb.AppendLine(SQLGeneration.GetSQLDropColumn(oldC));
							sb.AppendLine("GO");
							sb.AppendLine();
						}
						else if (newC.DatabaseName != oldC.DatabaseName)
						{
							//RENAME COLUMN
							string sql = "if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + oldC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" +
													 "AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + newC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" + Environment.NewLine +
													 "EXEC sp_rename @objname = '" + newT.DatabaseName + "." + oldC.DatabaseName + "', @newname = '" + newC.DatabaseName + "', @objtype = 'COLUMN'";
							if (!querylist.Contains(sql))
							{
								querylist.Add(sql);
								sb.AppendLine(sql);
								sb.AppendLine("GO");
								sb.AppendLine();
							}
						}

					}
					#endregion

					#region Add columns
					foreach (Reference newRef in newT.Columns)
					{
						Column newC = (Column)newRef.Object;
						Column oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
						if (oldC == null)
						{
							//ADD COLUMN
							sb.AppendLine(SQLGeneration.GetSQLAddColumn(newC));
							sb.AppendLine("GO");
							sb.AppendLine();
						}
						else if (newC.DatabaseName != oldC.DatabaseName)
						{
							//RENAME COLUMN
							string sql = "if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + oldC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" +
													 "AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + newC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" + Environment.NewLine +
													 "EXEC sp_rename @objname = '" + newT.DatabaseName + "." + oldC.DatabaseName + "', @newname = '" + newC.DatabaseName + "', @objtype = 'COLUMN'";
							if (!querylist.Contains(sql))
							{
								querylist.Add(sql);
								sb.AppendLine(sql);
								sb.AppendLine("GO");
								sb.AppendLine();
							}
						}

					}
					#endregion

					#region Modify Columns
					foreach (Reference newRef in newT.Columns)
					{
						Column newC = (Column)newRef.Object;
						Column oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
						if (oldC != null)
						{
							XmlDocument document = new XmlDocument();
							document.LoadXml("<a></a>");
							XmlNode n1 = XmlHelper.AddElement(document.DocumentElement, "q");
							XmlNode n2 = XmlHelper.AddElement(document.DocumentElement, "q");
							oldC.XmlAppend(n1);
							newC.XmlAppend(n2);
							Widgetsphere.Generator.Common.Util.XmlHelper.UpdateAttribute((XmlElement)n1, "createdDate", "");
							Widgetsphere.Generator.Common.Util.XmlHelper.UpdateAttribute((XmlElement)n2, "createdDate", "");
							if (n1.OuterXml != n2.OuterXml)
							{
								//MODIFY COLUMN
								sb.AppendLine(SQLGeneration.GetSQLModifyColumn(newC));
								sb.AppendLine("GO");
								sb.AppendLine();
							}
						}
					}
					#endregion

				}
			}
			#endregion

			return sb.ToString();
		}

		#endregion

		#region GetSQLSchema

		public static string GetSQLSchema(this Table item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		public static string GetSQLSchema(this TableComponent item)
		{
			if (string.IsNullOrEmpty(item.Parent.DBSchema)) return "dbo";
			return item.Parent.DBSchema;
		}

		public static string GetSQLSchema(this CustomStoredProcedure item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		public static string GetSQLSchema(this CustomView item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		#endregion

	}
}

